package co.th.bjc.md.helper;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;

import co.th.bjc.md.common.CommonUtils;
import co.th.bjc.md.common.ConnectDB;
import co.th.bjc.md.model.CustomerDetails;
import co.th.bjc.md.model.Person;

public class PersonHelper {
	
	Logger _log = Logger.getLogger(getClass().getName());
	
	public Person save(Connection conn, Person person) throws Exception{
		boolean isConnNull = false;
		ConnectDB connectDB = null;
		try {
			if (conn == null){
				connectDB = new ConnectDB();
				conn = connectDB.getConnection();
				conn.setAutoCommit(true);
				isConnNull = true;
			}

			person = insert(conn, person);
		} catch (Exception ex) {
			ex.printStackTrace();
			_log.error("Save Person failed. Detail : " + ex.toString());
		}finally{
			if (conn != null && isConnNull){
				connectDB.close();
			}
		}
		return person;
	}
	
	public List<CustomerDetails> save(List<CustomerDetails> customerDetails) throws Exception{
		return null;
	}
	
	private static final String SQL_INSERT = "INSERT INTO person(person_code, person_title, person_first_name, person_last_name, telephone, email, imp_code, rp_code, remark, create_by, create_date, update_by, update_date) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
	private synchronized Person insert(Connection conn, Person person) throws Exception {
		PreparedStatement stmt = null;
		try {
			BigDecimal personCode = generatePersonCode(conn);
			_log.info("Person Code : " + personCode);
			person.setPersonCode(personCode);
			
			stmt = conn.prepareStatement(SQL_INSERT);
			
			stmt.setBigDecimal(1, person.getPersonCode());
			stmt.setString(2, person.getPersonTitle());
			stmt.setString(3, person.getPersonFirstName());
			stmt.setString(4, person.getPersonLastName());
			stmt.setString(5, person.getTelephone());
			stmt.setString(6, person.getEmail());
			stmt.setBigDecimal(7, person.getImp().getImpCode());
			stmt.setBigDecimal(8, person.getRp().getRpCode());
			stmt.setString(9, person.getRemark());
			stmt.setString(10, CommonUtils.chkStringIsNull(person.getCreateBy())?"SYSTEM":person.getCreateBy());
			stmt.setDate(11, new java.sql.Date(new Date().getTime()));
			stmt.setString(12, "SYSTEM");
			stmt.setDate(13, new java.sql.Date(new Date().getTime()));
			
			stmt.executeUpdate();
			_log.warn("Insert Person By PersonCode : " + person.getPersonCode());
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (stmt != null) stmt.close();
		}
		return person;
	}
	
	private BigDecimal generatePersonCode(Connection conn) throws Exception{
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try{
			String sql = "SELECT MAX(person_code) + 1 AS person_code FROM person";
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			if(rs.next()){
				return rs.getBigDecimal("person_code");
			}
			return null;
		}catch(Exception ex){
			_log.error("generateCustomerDataID --> " + ex.toString());
			return null;
		}finally{
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
		}
	}
	
	public Person findPersonByPersonCode(BigDecimal personCode) throws Exception{
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Person person = null;
		ConnectDB connectDB = null;
		try{
			String sql = "SELECT * FROM person WHERE person_code = ?";
			_log.info("SQL : " + sql);
			
			connectDB = new ConnectDB();
			conn = connectDB.getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setBigDecimal(1, personCode);
			
			rs = stmt.executeQuery();
			if (rs.next()){
				person = this.setPerson(rs);
			}
		}catch(Exception ex){
			_log.error("findPersonByPersonCode --> " + ex.toString());
			ex.printStackTrace();
		}finally{
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			connectDB.close();
		}
		return person;
	}
	
	public List<Person> findPersonByPresident() throws Exception{
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<Person> listPerson = new ArrayList<Person>(0);
		ConnectDB connectDB = null;
		try{
			String sql = "SELECT * FROM person WHERE person_type = ?";
			_log.info("SQL : " + sql);
			
			connectDB = new ConnectDB();
			conn = connectDB.getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setBigDecimal(1, BigDecimal.valueOf(1));
			
			rs = stmt.executeQuery();
			while (rs.next()){
				listPerson.add(this.setPerson(rs));
			}
		}catch(Exception ex){
			_log.error("findPersonByPresident --> " + ex.toString());
			ex.printStackTrace();
		}finally{
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			connectDB.close();
		}
		return listPerson;
	}
	
	private Person setPerson(ResultSet rs) throws Exception{
		Person person = new Person();
		person.setPersonCode(rs.getBigDecimal("person_code"));
		person.setPersonTitle(rs.getString("person_title"));
		person.setPersonFirstName(rs.getString("person_first_name"));
		person.setPersonLastName(rs.getString("person_last_name"));
		person.setPersonType(rs.getBigDecimal("person_type"));
		person.setTelephone(rs.getString("telephone"));
		person.setEmail(rs.getString("email"));
		person.setImp(new IMPRatingHelper().findImpRatingByImpCode(rs.getBigDecimal("imp_code")));
		person.setRp(new RPRatingHelper().findRPRatingByRPCode(rs.getBigDecimal("rp_code")));
		person.setRemark(rs.getString("remark"));
		person.setCreateBy(rs.getString("create_by"));
		person.setCreateDate(rs.getDate("create_date"));
		person.setUpdateBy(rs.getString("update_by"));
		person.setUpdateDate(rs.getDate("update_date"));
		return person;
	}
}
